package lsy.Dao;

import lsy.tools.PageInfo;
import lsy.tools.PropertiesUtils;
import lsy.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class StudentDao {
    public void add(Student student) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "insert into students(studentId,stuNum,stuName,pawd) values(?,?,?,?)";
        queryRunner.update(sql,student.getstudentId(),student.getstuNum(),student.getstuName(),student.getpawd());
    }
    public void delete(Integer studentId) throws SQLException{
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "delete from students where studentId=?";
        queryRunner.update(sql,studentId);
    }
    public void update(Student student) throws SQLException{
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "update students set stuNum=?,stuName =? where studentId=?";
        queryRunner.update(sql,student.getstuNum(),student.getstuName(),student.getstudentId());
    }

    public PageInfo<Student> list(Student student, PageInfo<Student> pageInfo) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String _sql = "";
        List<Object> _list = new ArrayList<Object>();
        if(student.getstudentId() != null) {
            _sql += " and studentId = ?";
            _list.add(student.getstudentId());
        }
        if(StringUtils.isNoneBlank(student.getstuName())) {
            _sql += " and stuName like ?";
            _list.add("%"+student.getstuName()+"%");
        }
        if(StringUtils.isNoneBlank(student.getstuNum())) {
            _sql += " and stuNum like ?";
            _list.add("%"+student.getstuNum()+"%");
        }
        //_list转数组
        Object[] arr = new Object[_list.size()];
        for (int i=0;i<_list.size();i++) {
            arr[i] = _list.get(i);
        }
        String sql = "select * from students where 1=1 "+_sql+" limit  "+(pageInfo.getPageNo()-1)*pageInfo.getPageSize()+" , "+pageInfo.getPageSize();
        List<Student> list = queryRunner.query(sql, new BeanListHandler<>(Student.class),arr);

        pageInfo.setList(list);
        pageInfo.setTotalCount(this.count(student));
        return pageInfo;
    }

    public Student findById(Integer studentId) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select * from students where studentId=?";
        Student student =queryRunner.query(sql,new BeanHandler<>(Student.class),studentId);
        return student;
    }
    public Student login(String stuNum,String pawd) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select * from students where stuNum=? and pawd=?";
        Student student =queryRunner.query(sql,new BeanHandler<>(Student.class),stuNum,pawd);
        return student;
    }

    public Long count(Student student) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String _sql = "";
        List<Object> _list = new ArrayList<Object>();
        if(student.getstudentId() != null) {
            _sql += " and studentId = ?";
            _list.add(student.getstudentId());
        }
        if(StringUtils.isNoneBlank(student.getstuName())) {
            _sql += " and stuName like ?";
            _list.add("%"+student.getstuName()+"%");
        }
        if(StringUtils.isNoneBlank(student.getstuNum())) {
            _sql += " and stuNUM like ?";
            _list.add("%"+student.getstuNum()+"%");
        }
        //_list转数组
        Object[] arr = new Object[_list.size()];
        for (int i=0;i<_list.size();i++) {
            arr[i] = _list.get(i);
        }
        String sql = "select count(*) from students where 1=1 "+_sql;
        Long count = (Long)queryRunner.query(sql,new ScalarHandler(),arr);
        return count;
    }


}

